package team8.database;

import team8.helpers.timehelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.provider.BaseColumns;
import android.util.Log;

public class databaseconnector extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "fitdroid";
	private static final int DATABASE_VERSION = 1;

	// Table name
	public static final String GPSDATA = "gpsdata";
	public static final String TCONSITS = "tconsists";
	public static final String TRACK = "track";
	public static final String USER = "userdata";

	// Columns
	public static final String LATITUDE = "latitude";
	public static final String LONGITUDE = "longitude";
	public static final String ALTITUDE = "altitude";
	public static final String DATE = "date";
	public static final String TIME = "time";
	
	private timehelper time= new timehelper();
	private int gpslocationcounter =0; 

	public databaseconnector(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table " + GPSDATA + "( " + BaseColumns._ID
				+ " integer primary key autoincrement, " + LATITUDE + " varchar(30), "
				+ LONGITUDE + " varchar(30), "+ ALTITUDE + " varchar(30), "+ DATE + " varchar(30), "+ TIME + " varchar(30));";
		Log.d("GPSData", "onCreate: " + sql);
		db.execSQL(sql);
		
		sql ="create table " + TCONSITS + " (_id integer primary key autoincrement, trackid integer, gpsdataid integer)";
		db.execSQL(sql);
		
		sql ="CREATE TRIGGER delete_GPSDATA DELETE ON "+GPSDATA
			 + " BEGIN "
		     +		"DELETE FROM "+TCONSITS+" WHERE gpsdataid = old._id; "
		     + "END;";
		db.execSQL(sql);
		
		
		sql ="create table " + TRACK + " (_id integer primary key autoincrement, name varchar(30), difficulty varchar(30))";
		db.execSQL(sql);
		
		sql ="create table " + USER + " (_id integer primary key autoincrement, name varchar(30), age integer, weight integer, distance float, time integer)";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		if (oldVersion == 1) 
			sql = "alter table " + GPSDATA + " add note text;";
		if (oldVersion == 2)
			sql = "";

		Log.d("EventsData", "onUpgrade	: " + sql);
		if (sql != null)
			db.execSQL(sql);
	}
	
	public void insertnewgpsposition(SQLiteDatabase db,Location newlocation, int trackid) {
		// insert only every fifth elemet into the database to increase performance ??
		if (gpslocationcounter==5)
		{		 
			    //Gpsdata Table
			    ContentValues values = new ContentValues();
			    values.put(databaseconnector.LATITUDE, newlocation.getLatitude()); 
			    values.put(databaseconnector.LONGITUDE, newlocation.getLongitude());
			    values.put(databaseconnector.ALTITUDE, newlocation.getAltitude());
			    values.put(databaseconnector.DATE, time.now ("yyyy.MM.dd"));
			    values.put(databaseconnector.TIME, time.now("H:mm:ss"));
			    Long gpsid=db.insert(databaseconnector.GPSDATA, null, values);
			    
			    //Tconstist table
			   
			    values = new ContentValues();
			    values.put("trackid", trackid);
			    values.put("gpsdataid", gpsid);    
			    db.insert(databaseconnector.TCONSITS, null, values);
			    
			    
			    // update gpslocationcounter
			    gpslocationcounter=0;
		}
		else
		{
			gpslocationcounter++;
		}
}
	
	public int newtrack (SQLiteDatabase db)
	{
		ContentValues values = new ContentValues();
		values.put("name", "");
		values.put("difficulty", "0");
		return (int) db.insert(databaseconnector.TRACK, null, values);
	}

}
